package com.mysimpatico.sqlwrapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.ListIterator;

public class JDBC {

        /**
         * Constant to pass to DatabaseMetaData.getTables() to fetch
         * just tables.
         */
        public static final String[] GET_TABLES_TABLE = new String[]{"TABLE"};
        /**
         * Constant to pass to DatabaseMetaData.getTables() to fetch
         * just views.
         */
        public static final String[] GET_TABLES_VIEW = new String[]{"VIEW"};
        /**
         * Constant to pass to DatabaseMetaData.getTables() to fetch
         * just synonyms.
         */
        public static final String[] GET_TABLES_SYNONYM =
                new String[]{"SYNONYM"};
        /**
         * Types.SQLXML value without having to compile with JDBC4.
         */
        public static final int SQLXML = 2009;

        public static void dropSchemas(Connection conn) throws SQLException {

            final DatabaseMetaData dmd = conn.getMetaData();

            SQLException sqle = null;
            // Loop a number of arbitary times to catch cases
            // where objects are dependent on objects in
            // different schemas.
            for (int count = 0; count < 5; count++) {
                // Fetch all the user schemas into a list
                final ArrayList<String> schemas = new ArrayList<String>();
                ResultSet rs = dmd.getSchemas();
                while (rs.next()) {

                    final String schema = rs.getString("TABLE_SCHEM");
                    if (schema.startsWith("SYS")) {
                        continue;
                    }
                    if (schema.equals("SQLJ")) {
                        continue;
                    }
                    if (schema.equals("NULLID")) {
                        continue;
                    }

                    schemas.add(schema);
                }
                rs.close();

                // DROP all the user schemas.
                sqle = null;
                for (String schema : schemas) {
                    System.out.println("destroying schema " + schema);
                    try {
                        JDBC.dropSchema(dmd, schema, conn);
                    } catch (SQLException e) {
                        sqle = e;
                    }
                }
                // No errors means all the schemas we wanted to
                // drop were dropped, so nothing more to do.
                if (sqle == null) {
                    return;
                }
            }
            throw sqle;
        }

        /**
         * Drop a database schema by dropping all objects in it
         * and then executing DROP SCHEMA. If the schema is
         * APP it is cleaned but DROP SCHEMA is not executed.
         *
         * TODO: Handle dependencies by looping in some intelligent
         * way until everything can be dropped.
         *

         *
         * @param dmd DatabaseMetaData object for database
         * @param schema Name of the schema
         * @throws SQLException database error
         */
        private static void dropSchema(DatabaseMetaData dmd, String schema, final Connection conn) throws SQLException {
            Statement s = conn.createStatement();

            // Functions - not supported by JDBC meta data until JDBC 4
            // Need to use the CHAR() function on A.ALIASTYPE
            // so that the compare will work in any schema.
            PreparedStatement psf = conn.prepareStatement(
                    "SELECT ALIAS FROM SYS.SYSALIASES A, SYS.SYSSCHEMAS S"
                    + " WHERE A.SCHEMAID = S.SCHEMAID "
                    + " AND CHAR(A.ALIASTYPE) = ? "
                    + " AND S.SCHEMANAME = ?");
            psf.setString(1, "F");
            psf.setString(2, schema);
            ResultSet rs = psf.executeQuery();
            dropUsingDMD(s, rs, schema, "ALIAS", "FUNCTION");

            // Procedures
            rs = dmd.getProcedures((String) null,
                    schema, (String) null);

            dropUsingDMD(s, rs, schema, "PROCEDURE_NAME", "PROCEDURE");

            // Views
            rs = dmd.getTables((String) null, schema, (String) null,
                    GET_TABLES_VIEW);

            dropUsingDMD(s, rs, schema, "TABLE_NAME", "VIEW");

            // Tables
            rs = dmd.getTables((String) null, schema, (String) null,
                    GET_TABLES_TABLE);

            dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");

            // At this point there may be tables left due to
            // foreign key constraints leading to a dependency loop.
            // Drop any constraints that remain and then drop the tables.
            // If there are no tables then this should be a quick no-op.
            ResultSet table_rs = dmd.getTables((String) null, schema, (String) null,
                    GET_TABLES_TABLE);

            while (table_rs.next()) {
                String tablename = table_rs.getString("TABLE_NAME");
                rs = dmd.getExportedKeys((String) null, schema, tablename);
                while (rs.next()) {
                    short keyPosition = rs.getShort("KEY_SEQ");
                    if (keyPosition != 1) {
                        continue;
                    }
                    String fkName = rs.getString("FK_NAME");
                    // No name, probably can't happen but couldn't drop it anyway.
                    if (fkName == null) {
                        continue;
                    }
                    String fkSchema = rs.getString("FKTABLE_SCHEM");
                    String fkTable = rs.getString("FKTABLE_NAME");

                    String ddl = "ALTER TABLE "
                            + JDBC.escape(fkSchema, fkTable)
                            + " DROP FOREIGN KEY "
                            + JDBC.escape(fkName);
                    s.executeUpdate(ddl);
                }
                rs.close();
            }
            table_rs.close();
            conn.commit();

            // Tables (again)
            rs = dmd.getTables((String) null, schema, (String) null,
                    GET_TABLES_TABLE);
            dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");

            // drop UDTs
            psf.setString(1, "A");
            psf.setString(2, schema);
            rs = psf.executeQuery();
            dropUsingDMD(s, rs, schema, "ALIAS", "TYPE");
            psf.close();

            // Synonyms - need work around for DERBY-1790 where
            // passing a table type of SYNONYM fails.
            rs = dmd.getTables((String) null, schema, (String) null,
                    GET_TABLES_SYNONYM);

            dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");

            // sequences
            if (sysSequencesExists(conn)) {
                psf = conn.prepareStatement(
                        "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S"
                        + " WHERE A.SCHEMAID = S.SCHEMAID "
                        + " AND S.SCHEMANAME = ?");
                psf.setString(1, schema);
                rs = psf.executeQuery();
                dropUsingDMD(s, rs, schema, "SEQUENCENAME", "SEQUENCE");
                psf.close();
            }

            // Finally drop the schema if it is not APP
            if (!schema.equals("APP")) {
                s.executeUpdate("DROP SCHEMA " + JDBC.escape(schema) + " RESTRICT");
            }
            conn.commit();
            s.close();
        }

        /**
         * Return true if the SYSSEQUENCES table exists.
         */
        private static boolean sysSequencesExists(Connection conn) throws SQLException {
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(
                        "select count(*) from sys.systables t, sys.sysschemas s\n"
                        + "where t.schemaid = s.schemaid\n"
                        + "and ( cast(s.schemaname as varchar(128)))= 'SYS'\n"
                        + "and ( cast(t.tablename as varchar(128))) = 'SYSSEQUENCES'");
                rs = ps.executeQuery();
                rs.next();
                return (rs.getInt(1) > 0);
            } finally {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            }
        }

        /**
         * DROP a set of objects based upon a ResultSet from a
         * DatabaseMetaData call.
         *
         * TODO: Handle errors to ensure all objects are dropped,
         * probably requires interaction with its caller.
         *
         * @param s Statement object used to execute the DROP commands.
         * @param rs DatabaseMetaData ResultSet
         * @param schema Schema the objects are contained in
         * @param mdColumn The column name used to extract the object's
         * name from rs
         * @param dropType The keyword to use after DROP in the SQL statement
         * @throws SQLException database errors.
         */
        private static void dropUsingDMD(Statement s, ResultSet rs, String schema, String mdColumn, String dropType) throws SQLException {
            String dropLeadIn = "DROP " + dropType + " ";

            // First collect the set of DROP SQL statements.
            ArrayList ddl = new ArrayList();
            while (rs.next()) {
                String objectName = rs.getString(mdColumn);
                String raw = dropLeadIn + JDBC.escape(schema, objectName);
                if ("TYPE".equals(dropType) || "SEQUENCE".equals(dropType)) {
                    raw = raw + " restrict ";
                }
                ddl.add(raw);
            }
            rs.close();
            if (ddl.isEmpty()) {
                return;
            }

            // Execute them as a complete batch, hoping they will all succeed.
            s.clearBatch();
            int batchCount = 0;
            for (Object sql : ddl) {
                if (sql != null) {
                    s.addBatch(sql.toString());
                    batchCount++;
                }
            }

            int[] results;
            boolean hadError;
            try {
                results = s.executeBatch();
                assert (results != null);
                assert (batchCount == results.length);
                hadError = false;
            } catch (BatchUpdateException batchException) {
                results = batchException.getUpdateCounts();
                assert (results != null);
                assert (results.length <= batchCount);
                hadError = true;
            }

            // Remove any statements from the list that succeeded.
            boolean didDrop = false;
            for (int i = 0; i < results.length; i++) {
                int result = results[i];
                if (result == Statement.EXECUTE_FAILED) {
                    hadError = true;
                } else if (result == Statement.SUCCESS_NO_INFO || result >= 0) {
                    didDrop = true;
                    ddl.set(i, null);
                }
            }
            s.clearBatch();
            if (didDrop) {
                // Commit any work we did do.
                // s.getConnection().commit();
            }

            // If we had failures drop them as individual statements
            // until there are none left or none succeed. We need to
            // do this because the batch processing stops at the first
            // error. This copes with the simple case where there
            // are objects of the same type that depend on each other
            // and a different drop order will allow all or most
            // to be dropped.
            if (hadError) {
                do {
                    hadError = false;
                    didDrop = false;
                    for (ListIterator i = ddl.listIterator(); i.hasNext();) {
                        Object sql = i.next();
                        if (sql != null) {
                            try {
                                s.executeUpdate(sql.toString());
                                i.set(null);
                                didDrop = true;
                            } catch (SQLException e) {
                                hadError = true;
                            }
                        }
                    }/*
                    if (didDrop) {
                        //  s.getConnection().commit();
                    }*/
                } while (hadError && didDrop);
            }
        }

        /**
         * Escape a non-qualified name so that it is suitable
         * for use in a SQL query executed by JDBC.
         */
        public static String escape(String name) {
            StringBuffer buffer = new StringBuffer(name.length() + 2);
            buffer.append('"');
            for (int i = 0; i < name.length(); i++) {
                char c = name.charAt(i);
                // escape double quote characters with an extra double quote
                if (c == '"') {
                    buffer.append('"');
                }
                buffer.append(c);
            }
            buffer.append('"');
            return buffer.toString();
        }

        /**
         * Escape a schama-qualified name so that it is suitable
         * for use in a SQL query executed by JDBC.
         */
        public static String escape(String schema, String name) {
            return escape(schema) + "." + escape(name);
        }

        /**
         * Return Type name from jdbc type
         *
         * @param jdbcType  jdbc type to translate
         */
        public static String sqlNameFromJdbc(int jdbcType) {
            switch (jdbcType) {
                case Types.BIT:
                    return "Types.BIT";
                case Types.BOOLEAN:
                    return "Types.BOOLEAN";
                case Types.TINYINT:
                    return "Types.TINYINT";
                case Types.SMALLINT:
                    return "SMALLINT";
                case Types.INTEGER:
                    return "INTEGER";
                case Types.BIGINT:
                    return "BIGINT";

                case Types.FLOAT:
                    return "Types.FLOAT";
                case Types.REAL:
                    return "REAL";
                case Types.DOUBLE:
                    return "DOUBLE";

                case Types.NUMERIC:
                    return "Types.NUMERIC";
                case Types.DECIMAL:
                    return "DECIMAL";

                case Types.CHAR:
                    return "CHAR";
                case Types.VARCHAR:
                    return "VARCHAR";
                case Types.LONGVARCHAR:
                    return "LONG VARCHAR";
                case Types.CLOB:
                    return "CLOB";

                case Types.DATE:
                    return "DATE";
                case Types.TIME:
                    return "TIME";
                case Types.TIMESTAMP:
                    return "TIMESTAMP";

                case Types.BINARY:
                    return "CHAR () FOR BIT DATA";
                case Types.VARBINARY:
                    return "VARCHAR () FOR BIT DATA";
                case Types.LONGVARBINARY:
                    return "LONG VARCHAR FOR BIT DATA";
                case Types.BLOB:
                    return "BLOB";

                case Types.OTHER:
                    return "Types.OTHER";
                case Types.NULL:
                    return "Types.NULL";
                default:
                    return String.valueOf(jdbcType);
            }
        }
    }
